/*This is sql script for orchard_system,please run this script to create database and init test data.
 *Note:This file must saved use UTF-8 format,ASCII format will get errors
 *@Author:WangYun(waylife365@gmail.com)
 *@CreateDate:2013/5/15
 *@UpdateDate:2013/5/28
 * How to use this file,login mysql dos window,and use "source filepath/orchard_system.sql" command
 **/
DROP DATABASE IF EXISTS orchard_system;
CREATE DATABASE orchard_system;
USE orchard_system;

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `boundary`
-- ----------------------------
DROP TABLE IF EXISTS `boundary`;
CREATE TABLE `boundary` (
  `boundaryid` int(11) NOT NULL AUTO_INCREMENT,
  `boundaryindex` int(11) NOT NULL,
  `orchardid` int(11) NOT NULL,
  `longitude` double NOT NULL,
  `latitude` double NOT NULL,
  `updatetime` datetime NOT NULL,
  PRIMARY KEY (`boundaryid`),
  KEY `boundary_orchard` (`orchardid`),
  CONSTRAINT `boundary_orchard` FOREIGN KEY (`orchardid`) REFERENCES `orchard` (`orchardid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;



-- ----------------------------
-- Table structure for `device`
-- ----------------------------
DROP TABLE IF EXISTS `device`;
CREATE TABLE `device` (
  `deviceid` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL,
  `machineid` varchar(25) NOT NULL,
  `description` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`deviceid`),
  KEY `device_user` (`userid`),
  CONSTRAINT `device_user` FOREIGN KEY (`userid`) REFERENCES `user` (`userid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of device
-- ----------------------------
INSERT INTO `device` VALUES ('1', '1', '868033012274397', 'wangyun的设备');
INSERT INTO `device` VALUES ('2', '1', '000000000000000', '默认设备');

-- ----------------------------
-- Table structure for `orchard`
-- ----------------------------
DROP TABLE IF EXISTS `orchard`;
CREATE TABLE `orchard` (
  `orchardid` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) DEFAULT NULL,
  `description` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`orchardid`),
  KEY `orchard_user` (`userid`),
  CONSTRAINT `orchard_user` FOREIGN KEY (`userid`) REFERENCES `user` (`userid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of orchard
-- ----------------------------
INSERT INTO `orchard` VALUES ('1', '1', 'default的果园');
INSERT INTO `orchard` VALUES ('2', '1', 'wangyun的果园');

-- ----------------------------
-- Table structure for `tree`
-- ----------------------------
DROP TABLE IF EXISTS `tree`;
CREATE TABLE `tree` (
  `treeid` int(11) NOT NULL AUTO_INCREMENT,
  `rfid` int(11) NOT NULL,
  `orchardid` int(11) NOT NULL,
  `longitude` double NOT NULL,
  `latitude` double NOT NULL,
  `updatetime` datetime NOT NULL,
  PRIMARY KEY (`treeid`),
  KEY `tree_orchard` (`orchardid`),
  CONSTRAINT `tree_orchard` FOREIGN KEY (`orchardid`) REFERENCES `orchard` (`orchardid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;



-- ----------------------------
-- Table structure for `treeinfo`
-- ----------------------------
DROP TABLE IF EXISTS `treeinfo`;
CREATE TABLE `treeinfo` (
  `infoid` int(11) NOT NULL AUTO_INCREMENT,
  `rfid` int(11) NOT NULL,
  `soiltemperature` double NOT NULL,
  `soilhumidity` double NOT NULL,
  `airtemperature` double NOT NULL,
  `airhumidity` double NOT NULL,
  `collectiontime` datetime NOT NULL,
  PRIMARY KEY (`infoid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;



-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `userid` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `password` varchar(40) NOT NULL,
  `description` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'wangyun', '63A9F0EA7BB98050796B649E85481845', 'wangyun');

-- ----------------------------
-- Table structure for `usertoken`
-- ----------------------------
DROP TABLE IF EXISTS `usertoken`;
CREATE TABLE `usertoken` (
  `tokenid` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL,
  `tokencontent` varchar(40) NOT NULL,
  `expiretime` datetime NOT NULL,
  PRIMARY KEY (`tokenid`),
  KEY `usertoken_user` (`userid`),
  CONSTRAINT `usertoken_user` FOREIGN KEY (`userid`) REFERENCES `user` (`userid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=255 DEFAULT CHARSET=utf8;


/*Init Test Data*/
insert into boundary values(null,0,1,116.359487,40.009872,'2013-06-03 15:36:31');
insert into boundary values(null,1,1,116.359362,40.011755,'2013-06-03 15:36:31');
insert into boundary values(null,2,1,116.359236,40.013495,'2013-06-03 15:36:31');
insert into boundary values(null,3,1,116.360404,40.013537,'2013-06-03 15:36:31');
insert into boundary values(null,4,1,116.361536,40.013592,'2013-06-03 15:36:31');
insert into boundary values(null,5,1,116.362703,40.013689,'2013-06-03 15:36:31');
insert into boundary values(null,6,1,116.36344,40.013717,'2013-06-03 15:36:31');
insert into boundary values(null,7,1,116.365273,40.013965,'2013-06-03 15:36:31');
insert into boundary values(null,8,1,116.365686,40.013198,'2013-06-03 15:36:31');
insert into boundary values(null,9,1,116.36706,40.011748,'2013-06-03 15:36:31');
insert into boundary values(null,10,1,116.366377,40.012446,'2013-06-03 15:36:31');
insert into boundary values(null,11,1,116.367366,40.011437,'2013-06-03 15:36:31');
insert into boundary values(null,12,1,116.368264,40.010463,'2013-06-03 15:36:31');
insert into boundary values(null,13,1,116.369441,40.009606,'2013-06-03 15:36:31');
insert into boundary values(null,14,1,116.370608,40.008543,'2013-06-03 15:36:31');
insert into boundary values(null,15,1,116.371183,40.007872,'2013-06-03 15:36:31');
insert into boundary values(null,16,1,116.371192,40.007499,'2013-06-03 15:36:31');
insert into boundary values(null,17,1,116.370626,40.007472,'2013-06-03 15:36:31');
insert into boundary values(null,18,1,116.369198,40.007417,'2013-06-03 15:36:31');
insert into boundary values(null,19,1,116.366252,40.007265,'2013-06-03 15:36:31');
insert into boundary values(null,20,1,116.364877,40.007202,'2013-06-03 15:36:31');
insert into boundary values(null,21,1,116.361769,40.006995,'2013-06-03 15:36:31');
insert into boundary values(null,22,1,116.360988,40.006933,'2013-06-03 15:36:31');
insert into boundary values(null,23,1,116.359487,40.006898,'2013-06-03 15:36:31');
insert into boundary values(null,24,1,116.359523,40.007914,'2013-06-03 15:36:31');
insert into boundary values(null,25,1,116.359487,40.009206,'2013-06-03 15:36:31');
insert into boundary values(null,26,1,116.359478,40.009827,'2013-06-03 15:36:31');

insert into tree values(null,1,1,116.360026,40.00989,'2013-06-03 15:36:31');
insert into tree values(null,23,1,116.361149,40.009931,'2013-06-03 15:36:31');
insert into tree values(null,21,1,116.362748,40.010007,'2013-06-03 15:36:31');
insert into tree values(null,11,1,116.364662,40.01009,'2013-06-03 15:36:31');
insert into tree values(null,34,1,116.366728,40.010152,'2013-06-03 15:36:31');
insert into tree values(null,24,1,116.366925,40.009178,'2013-06-03 15:36:31');
insert into tree values(null,53,1,116.363611,40.009081,'2013-06-03 15:36:31');
insert into tree values(null,13,1,116.36211,40.00886,'2013-06-03 15:36:31');
insert into tree values(null,245,1,116.360844,40.008777,'2013-06-03 15:36:31');
insert into tree values(null,125,1,116.36468,40.008211,'2013-06-03 15:36:31');
